﻿--客服成单跟踪列表
CREATE PROCEDURE [dbo].[proc_Orders_Track_GetList_Kf]
	
	@StartIndex int,
	@EndIndex int,
	@MainProjectId int,
	@SText nvarchar(50),
	@StartDate nvarchar(50),
	@EndDate nvarchar (50),
	@State int, --全部 2 完成 1 未完成 0
	@UserName nvarchar(50)
as
begin
;WITH list As(Select ROW_NUMBER() OVER (ORDER BY o.Id DESC)AS Row,
	o.id,
	o.oid,
	o.customerId,
	isnull((select customer.customername from customer where customer.customerid=o.customerId),'...') as customername,
	(select e.ename from employee e where e.username=o.username) as ename,
	o.FixtureDate,
	(select product.title from product where product.id=o.MainProjectId) AS MainProjectName,
	(select e.ename from employee e where e.username=o.trackkf) as trackkf,
	(select count(project.id) from project where project.orderid=o.oid) as ProjectCount,
	(select count(project.id) from project where project.orderid=o.oid and project.isfinish=1) as ProjectFinishCount
from 
	orders o
where
	(Convert(varchar(10),FixtureDate,120)>=@StartDate and Convert(varchar(10),FixtureDate,120)<=@EndDate)
	and (isnull((Select CustomerName From Customer Where CustomerId=o.CustomerId),'...') like '%'+@SText+'%')
	and (@MainProjectId=0 or MainProjectId=@MainProjectId)
	and ((case when ((select count(project.id) from project where project.orderid=o.oid)>0) and  (select count(project.id) from project where project.orderid=o.oid and project.isfinish=0 )=0 then 1 else 0 end ) = @state or @state=2)
	and TrackKf = @UserName

)

Select * From list Where Row between @StartIndex and @EndIndex Order By Row 
	end
	RETURN
